from . import models
from .global_variable import *
from django.core.exceptions import ObjectDoesNotExist
from django.core import serializers
from django.db.models import Max
from django.forms.models import model_to_dict
import time, datetime, pandas as pd
from django.conf import settings
import sqlite3


def quick_select_stock_by_flow(days, in_or_out='in', query_code_list=[]):
    """
    说明：根据资金流向来选股
    参数：
        days:连续的天数
        in_or_out:资金流入或流出，默认为in，只传in或out，如不是这两个，函数返回None
    返回：股票代码列表
    """

    def select_eligible_stock():
        # 从预先分析表中读取ts_code，节约分析时间
        ts_code_list = []
        # 如果从已有结果中查询
        if len(query_code_list) > 0:
            ts_code_list = query_code_list
        else:  # 单独查询
            tmp_list = models.pre_analysis_moneyflow.objects.filter(in_or_out=in_or_out).values_list('ts_code')
            if (len(tmp_list) == 0):  # 不存在数据，则从原始表中读取ts_code
                stock_list = models.stock_basic.objects.all()
                for stock in stock_list:
                    ts_code_list.append(stock.ts_code)
            else:
                for i in tmp_list:
                    code = i[0]
                    ts_code_list.append(code)
                if (nums == 2):
                    return ts_code_list  # 如果是连续两天，则可以直接返回结果

        select_ts_code_list = []
        for ts_code in ts_code_list:
            tmp_list = models.moneyflow.objects.filter(ts_code=ts_code).order_by("-trade_date")[0:nums]
            if (len(tmp_list) < nums): continue  # 如果现金流少于连续天数则继续下一个
            i = 0
            while i < nums:
                try:
                    if (in_or_out == 'in'):
                        if (tmp_list[i].net_mf_amount >= 0):
                            i = i + 1
                        else:
                            break
                    if (in_or_out == 'out'):
                        if (tmp_list[i].net_mf_amount <= 0):
                            i = i + 1
                        else:
                            break
                except TypeError:
                    print(tmp_list[i].ts_code + '出错' + tmp_list[i].net_mf_amount)
            if (i >= nums):
                select_ts_code_list.append(tmp_list[0].ts_code)
        return select_ts_code_list

    nums = int(days)

    if (nums <= 0): nums = 1
    if not ((in_or_out == 'in') or (in_or_out == 'out')): return None
    ts_code_list = select_eligible_stock()
    name_list = select_name_by_ts_code(ts_code_list)

    return ts_code_list, name_list


def quick_select_stock_by_updown(days, up_or_down='up', query_code_list=[]):
    """
    根据股票涨跌选取函数
    :param days:连续涨跌天数
    :param up_or_down: 涨或跌，默认涨，输入为up或down，否则返回None
    :return: ts_code列表
    """

    def select_eligible_stock():
        ts_code_list = []
        if len(query_code_list) > 0:
            ts_code_list = query_code_list
        else:  # 单独查询
            tmp_list = models.pre_analysis_updown.objects.filter(up_or_down=up_or_down).values_list('ts_code')
            if (len(tmp_list) == 0):  # 不存在数据，则从原始表中读取ts_code
                stock_list = models.stock_basic.objects.all()
                for stock in stock_list:
                    ts_code_list.append(stock.ts_code)
            else:
                for i in tmp_list:
                    code = i[0]
                    ts_code_list.append(code)
                if (nums == 2):
                    return ts_code_list  # 如果是连续两天，则可以直接返回结果

        select_ts_code_list = []
        for ts_code in ts_code_list:
            tmp_list = models.daily.objects.filter(ts_code=ts_code).order_by("-trade_date")[0:nums]
            if (len(tmp_list) < nums): continue  # 如果交易少于连续天数则继续下一个
            i = 0
            while i < nums:
                try:
                    if (up_or_down == 'up'):
                        if (tmp_list[i].pct_chg >= 0):
                            i = i + 1
                        else:
                            break
                    if (up_or_down == 'down'):
                        if (tmp_list[i].pct_chg <= 0):
                            i = i + 1
                        else:
                            break
                except TypeError:
                    print(tmp_list[i].ts_code + '出错' + tmp_list[i].pct_chg)
            if (i >= nums):
                select_ts_code_list.append(tmp_list[0].ts_code)
        return select_ts_code_list

    nums = int(days)

    if (nums <= 0): nums = 1
    if not ((up_or_down == 'up') or (up_or_down == 'down')): return None
    ts_code_list = select_eligible_stock()
    name_list = select_name_by_ts_code(ts_code_list)
    return ts_code_list, name_list


def quick_select_stock_by_pct_chg(days, up_or_down='up', pct_chg=10, query_code_list=[]):
    """
    根据最近具体天数的总涨幅来选股
    :param days:最近天数
    :param up_or_down:涨还是跌
    :param pct_chg: 总涨幅
    :return: ts_code列表
    """

    def str_time_to_time(strTime=''):  # 将20190101格式的字符串转换成日期%Y-%m-%d返回,默认返回当前日期
        nowtmp = time.localtime(time.time())
        nowtmp = datetime.date(nowtmp.tm_year, nowtmp.tm_mon, nowtmp.tm_mday)
        if strTime == '':  return nowtmp
        tmp = time.strptime(strTime, '%Y%m%d')
        tmp = datetime.date(tmp.tm_year, tmp.tm_mon, tmp.tm_mday)
        return tmp

    def select_eligible_stock():
        ts_code_list = []
        if len(query_code_list) > 0:
            ts_code_list = query_code_list
        else:  # 单独查询
            tmp_list = models.pre_analysis_pct_chg.objects.filter(up_or_down=up_or_down).values_list('ts_code')
            if (len(tmp_list) == 0):  # 不存在数据，则从原始表中读取ts_code
                stock_list = models.stock_basic.objects.all()  # TODO:测试用，后期将0：500去掉

                for stock in stock_list:
                    ts_code_list.append(stock.ts_code)
            else:
                for i in tmp_list:
                    code = i[0]
                    ts_code_list.append(code)
                if ((pct_chg == 10) and (nums == 3)):
                    return ts_code_list  # 如果是连续两天，则可以直接返回结果

        select_ts_code_list = []
        for ts_code in ts_code_list:
            tradeinfo = models.daily.objects.filter(ts_code=ts_code).order_by("-trade_date")[0:nums]

            if (len(tradeinfo) == 0) or (len(tradeinfo) < nums): continue

            end_trade_close = tradeinfo[0].close
            start_trade_close = tradeinfo[nums - 1].close
            # end_trade_date = str_time_to_time(end_date)  # 转换成2019-10-10方便日期计算
            # tmp_nums = nums
            # for i in range(nums):
            #     tmp_day = end_trade_date - datetime.timedelta(i + 1)  # 计算开时计算日期
            #     if (datetime.date.isoweekday(tmp_day) == 6) or (datetime.date.isoweekday(
            #             tmp_day) == 7): tmp_nums = tmp_nums + 1  # 如果碰到周末，继续加1，还有节假日，太麻烦，主要检测周6周日
            # start_trade_date = end_trade_date - datetime.timedelta(tmp_nums)
            # end_trade_datef = datetime.datetime.strftime(end_trade_date, "%Y%m%d")  # 转换为20191010格式
            # start_trade_datef = datetime.datetime.strftime(start_trade_date, "%Y%m%d")
            # end_trade_close = models.daily.objects.get(trade_date=end_trade_datef, ts_code=ts_code).close
            # # 无限循环，直到找到相应数据，最多倒退查询连续天数
            # i = 0
            # while i < nums:
            #     try:
            #         start_trade_close = models.daily.objects.get(trade_date=start_trade_datef, ts_code=ts_code).close
            #         break
            #     except ObjectDoesNotExist:
            #         start_trade_date = start_trade_date - datetime.timedelta(1)
            #         start_trade_datef = datetime.datetime.strftime(start_trade_date, "%Y%m%d")
            #         i = i + 1
            #         start_trade_close = None

            # print(ts_code,end_trade_date,start_trade_date)
            if not (start_trade_close is None):
                if (up_or_down == 'up'):  # 判断涨
                    if ((end_trade_close - start_trade_close) > ((pct_chg / 100) * start_trade_close)):
                        select_ts_code_list.append(ts_code)
                    # print(ts_code, end_trade_date, start_trade_date,end_trade_close - start_trade_close)
                else:  # 判断跌幅
                    if ((end_trade_close - start_trade_close) < (0 - (pct_chg / 100) * start_trade_close)):
                        select_ts_code_list.append(ts_code)
        return select_ts_code_list

    nums = int(days)
    if (nums <= 0): nums = 1
    pct_chg = int(pct_chg)
    ts_code_list = select_eligible_stock()
    name_list = select_name_by_ts_code(ts_code_list)

    return ts_code_list, name_list


def select_name_by_ts_code(ts_code_list):
    """
    根据输入的ts_code列表，查询列表中股票名称
    :param ts_code_list: 股票tscode列表
    :return: 返回相应股票的最新交易日期的数据,如传入参数为空，则返回None
    """
    if (len(ts_code_list) == 0): return None
    stock_daily_list = []
    name_daily_list = []
    for tscode in ts_code_list:
        try:
            name = models.stock_basic.objects.filter(ts_code=tscode)[0]
        except:
            print(tscode)
            name_daily_list.append('error')
            continue
        name_daily_list.append(name.name)
    return name_daily_list


def pre_analysis_db(moneyflow_days=2, updown_days=2, pct_chg_days=3, pct_chg=10):
    """
    提前分析默认天数数据，并将数据存入提前分析表中，已便后期快速反馈查询，如果页面发生改变，该函数默认值需要修改
    :param moneyflow_days: 连续资金流天数
    :param updown_days: 连续涨跌天数
    :param pct_chg_days: 连续涨幅天数
    :param pct_chg: 涨幅百分比
    :return:
    """
    try:
        engine = settings.DATABASES['default']['NAME']
        cn = sqlite3.connect(engine)
    except:
        print('创建数据连接失败')
        return
    moneyflow_days = int(moneyflow_days)
    updown_days = int(updown_days)
    pct_chg_days = int(pct_chg_days)
    pct_chg = int(pct_chg)
    # 分析连续两天资金净流入或流出股票，并存入书库

    models.pre_analysis_moneyflow.objects.all().delete()
    code_list, name_list = quick_select_stock_by_flow(moneyflow_days, 'in')
    dic = {'ts_code': code_list, 'name': name_list, 'in_or_out': 'in', 'continues_days': moneyflow_days}
    df = pd.DataFrame(dic)
    df.to_sql(PRE_ANALYSIS_MONEYFLOW, cn, index=False, if_exists="append")
    print('资金连续2天流入股票共%d支' % len(df))
    a = '资金连续2天流入股票共%d支' % len(df) + '\n'
    code_list, name_list = quick_select_stock_by_flow(moneyflow_days, 'out')
    dic = {'ts_code': code_list, 'name': name_list, 'in_or_out': 'out', 'continues_days': moneyflow_days}
    df = pd.DataFrame(dic)
    df.to_sql(PRE_ANALYSIS_MONEYFLOW, cn, index=False, if_exists="append")
    print('资金连续2天流出股票共%d支' % len(df))
    a = a + '资金连续2天流出股票共%d支' % len(df) + '\n'

    # 分析连续两天涨跌股票，并存入书库
    models.pre_analysis_updown.objects.all().delete()
    code_list, name_list = quick_select_stock_by_updown(updown_days, 'up')
    dic = {'ts_code': code_list, 'name': name_list, 'up_or_down': 'up', 'continues_days': updown_days}
    df = pd.DataFrame(dic)
    df.to_sql(PRE_ANALYSIS_UPDOWN, cn, index=False, if_exists="append")
    print('连续2天上涨股票共%d支' % len(df))
    a = a + '连续2天上涨股票共%d支' % len(df) + '\n'
    code_list, name_list = quick_select_stock_by_updown(moneyflow_days, 'down')
    dic = {'ts_code': code_list, 'name': name_list, 'up_or_down': 'down', 'continues_days': updown_days}
    df = pd.DataFrame(dic)
    df.to_sql(PRE_ANALYSIS_UPDOWN, cn, index=False, if_exists="append")
    print('连续2天下跌股票共%d支' % len(df))
    a = a + '连续2天下跌股票共%d支' % len(df) + '\n'

    # 分析连续三天涨跌幅度为10%股票，并存入书库

    models.pre_analysis_pct_chg.objects.all().delete()
    code_list, name_list = quick_select_stock_by_pct_chg(pct_chg_days, 'up', pct_chg)
    dic = {'ts_code': code_list, 'name': name_list, 'up_or_down': 'up', 'continues_days': updown_days,
           'pct_chg': pct_chg}
    df = pd.DataFrame(dic)
    df.to_sql(PRE_ANALYSIS_PCT_CHG, cn, index=False, if_exists="append")
    print('连续3天涨幅10股票共%d支' % len(df))
    a = a + '连续3天涨幅10股票共%d支' % len(df) + '\n'
    code_list, name_list = quick_select_stock_by_pct_chg(pct_chg_days, 'down', pct_chg)
    dic = {'ts_code': code_list, 'name': name_list, 'up_or_down': 'down', 'continues_days': updown_days,
           'pct_chg': pct_chg}
    df = pd.DataFrame(dic)
    df.to_sql(PRE_ANALYSIS_PCT_CHG, cn, index=False, if_exists="append")
    print('连续3天跌幅10股票共%d支' % len(df))
    a = a + '连续3天跌幅10股票共%d支' % len(df) + '\n'
    # 计算每天的主力资金流向
    a = a+ pre_analysis_main_moneyflow()

    return a


def pre_analysis_main_moneyflow():
    """
    计算主力资金净流入数据，为快速查询提供帮助
    :return:
    """
    starttime = models.pre_analysis_main_moneyflow.objects.all().aggregate(Max('trade_date'))
    starttime = starttime['trade_date__max']

    if (starttime is None): starttime = '20190920'
    datelist = models.moneyflow.objects.values('trade_date').distinct().order_by('-trade_date')
    a = ''
    for dt in datelist:
        # print(dt['trade_date'])
        if (dt['trade_date'] <= starttime): break  # 如果日期已存在在数据库中，退出循环
        moneyflow_list = models.moneyflow.objects.filter(trade_date=dt['trade_date'])
        pre_main_list = []
        for mf in moneyflow_list:
            # print(mf.trade_date)
            main_in = mf.buy_lg_amount + mf.buy_elg_amount - mf.sell_elg_amount - mf.sell_lg_amount
            main_turnover_r = (mf.buy_lg_vol + mf.buy_elg_vol) * 100 / (
                    mf.buy_lg_vol + mf.buy_elg_vol + mf.buy_md_vol + mf.buy_sm_vol)
            pre_main = models.pre_analysis_main_moneyflow()
            pre_main.ts_code = mf.ts_code
            pre_main.trade_date = mf.trade_date
            pre_main.main_in = main_in
            pre_main.main_turnover_rate = main_turnover_r
            pre_main_list.append(pre_main)
        models.pre_analysis_main_moneyflow.objects.bulk_create(pre_main_list)
        print('分析' + dt['trade_date'] + '主力流入数据' + str(len(pre_main_list)) + '条')
        a = a + '分析' + dt['trade_date'] + '主力流入数据' + str(len(pre_main_list)) + '条' + '\n'
    return a


def query_stock_with_conditons(trade_date, conditon_list):
    """
    根据传入条件列表查询数据
    :param conditon_list: 条件列表,每个项为字典数据包括查询条件名称，操作大于或等于，条件值 例如 pct_chg  >=   5，代表查询涨幅大于等于5%的股票,需要和analysis.js文件项匹配
    :return: 返回股票代码列表和股票名称列表
    """
    if len(conditon_list) == 0:
        return None, None  # 无条件列表，则返回空

    daily_set = models.daily.objects.filter(trade_date=trade_date)
    code_list = []  # 初始集，符合交易日期的所有ts_code,用来做交集用的
    for daily in daily_set:
        code_list.append(daily.ts_code)

    # 依次匹配选股条件，通过set交集方法刷选符合所有条件的ts_code,并返回
    for dic_condition in conditon_list:  # 识别每一个条件,并从返回result_list中删除不符合条件的股票
        # 涨幅百分比
        if dic_condition['info'] == 'pct_chg':
            operation = dic_condition['operation']
            value = int(dic_condition['value'])
            if operation == "<=":
                eligible_list = models.daily.objects.filter(pct_chg__lte=value, trade_date=trade_date)
            else:
                eligible_list = models.daily.objects.filter(pct_chg__gte=value, trade_date=trade_date)
            compare_code_list = []
            for eligible in eligible_list:
                compare_code_list.append(eligible.ts_code)
            code_list = list(set(code_list) & set(compare_code_list))
        # 换手率（自由流通）
        if dic_condition['info'] == 'turnover_rate_f':
            operation = dic_condition['operation']
            value = dic_condition['value']
            if operation == "<=":
                eligible_list = models.daily_basic.objects.filter(turnover_rate_f__lte=value, trade_date=trade_date)
            else:
                eligible_list = models.daily_basic.objects.filter(turnover_rate_f__gte=value, trade_date=trade_date)
            compare_code_list = []
            for eligible in eligible_list:
                compare_code_list.append(eligible.ts_code)
            code_list = list(set(code_list) & set(compare_code_list))
        # 流通市值
        if dic_condition['info'] == 'circ_mv':
            operation = dic_condition['operation']
            value = dic_condition['value']
            if operation == "<=":
                eligible_list = models.daily_basic.objects.filter(circ_mv__lte=value, trade_date=trade_date)
            else:
                eligible_list = models.daily_basic.objects.filter(circ_mv__gte=value, trade_date=trade_date)
            compare_code_list = []
            for eligible in eligible_list:
                compare_code_list.append(eligible.ts_code)
            code_list = list(set(code_list) & set(compare_code_list))

        # 主力净流入
        if dic_condition['info'] == 'main_in':
            operation = dic_condition['operation']
            value = dic_condition['value']
            if operation == "<=":
                eligible_list = models.pre_analysis_main_moneyflow.objects.filter(main_in__lte=value,
                                                                                  trade_date=trade_date)
            else:
                eligible_list = models.pre_analysis_main_moneyflow.objects.filter(main_in__gte=value,
                                                                                  trade_date=trade_date)
            compare_code_list = []
            for eligible in eligible_list:
                compare_code_list.append(eligible.ts_code)
            code_list = list(set(code_list) & set(compare_code_list))

        # 主力占交易还手流通比
        if dic_condition['info'] == 'main_turnover_rate':
            operation = dic_condition['operation']
            value = dic_condition['value']
            if operation == "<=":
                eligible_list = models.pre_analysis_main_moneyflow.objects.filter(main_turnover_rate__lte=value,
                                                                                  trade_date=trade_date)
            else:
                eligible_list = models.pre_analysis_main_moneyflow.objects.filter(main_turnover_rate__gte=value,
                                                                                  trade_date=trade_date)
            compare_code_list = []
            for eligible in eligible_list:
                compare_code_list.append(eligible.ts_code)
            code_list = list(set(code_list) & set(compare_code_list))
        # 市净率
        if dic_condition['info'] == 'pe_ttm':
            operation = dic_condition['operation']
            value = dic_condition['value']
            if operation == "<=":
                eligible_list = models.daily_basic.objects.filter(pe_ttm__lte=value, trade_date=trade_date)
            else:
                eligible_list = models.daily_basic.objects.filter(pe_ttm__gte=value, trade_date=trade_date)
            compare_code_list = []
            for eligible in eligible_list:
                compare_code_list.append(eligible.ts_code)
            code_list = list(set(code_list) & set(compare_code_list))
        # 总流入资金
        if dic_condition['info'] == 'net_mf_amount':
            operation = dic_condition['operation']
            value = dic_condition['value']
            if operation == "<=":
                eligible_list = models.moneyflow.objects.filter(net_mf_amount__lte=value, trade_date=trade_date)
            else:
                eligible_list = models.moneyflow.objects.filter(net_mf_amount__gte=value, trade_date=trade_date)
            compare_code_list = []
            for eligible in eligible_list:
                compare_code_list.append(eligible.ts_code)
            code_list = list(set(code_list) & set(compare_code_list))
    name_list = select_name_by_ts_code(code_list)
    return code_list, name_list


def attention_stock_into_db(trade_date, ts_code_list):
    """
    将关注股票存入数据库，注意重复股票，通过求差集的方式，将未关注的股票存入数据库，a-b即求项在a中的但不再b中的元素
    :param ts_code_list:需要关注股票信息
    :return:返回是否插入成功
    """
    try:
        starttime = trade_date
        attention_stock_in_db = models.attention_stock.objects.filter(is_end=1)  # 查找数据库中仍处于关注状态的股票信息
        in_db_ts_code_list = []
        for attention_in_db in attention_stock_in_db:
            in_db_ts_code_list.append(attention_in_db.ts_code)
        ts_code_list = list(set(ts_code_list) - set(in_db_ts_code_list))  # 求差集
        attention_stock_list = []
        for ts_code in ts_code_list:
            attention_stock_tmp = models.attention_stock()
            attention_stock_tmp.ts_code = ts_code
            try:
                attention_stock_tmp.name = models.stock_basic.objects.get(ts_code=ts_code).name
            except ObjectDoesNotExist:
                attention_stock_tmp.name = ''
            attention_stock_tmp.start_attention_date = starttime
            day_close = models.daily.objects.filter(ts_code=ts_code, trade_date=starttime)[0].close
            attention_stock_tmp.start_attention_price = day_close
            attention_stock_tmp.is_end = True
            attention_stock_list.append(attention_stock_tmp)
        models.attention_stock.objects.bulk_create(attention_stock_list)
    except:
        return False

    return True
